package com.mini.repository;


import com.mini.model.BusinessBigLessonOrder;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Collection;
import java.util.Date;
import java.util.List;

public interface BusinessBigLessonOrderRepository extends JpaRepository<BusinessBigLessonOrder,String>, JpaSpecificationExecutor<BusinessBigLessonOrder> {


    List<BusinessBigLessonOrder> findByOrderNoIn(Collection<String> orderNo);

    @Query(value = "select boc.teacher_id as teacherId, \n" +
            "count(distinct case when order_state='15' and order_deposit_type!=1 then blo.order_no end) as bigOrderCount, \n" +
            "sum(case when order_state='15'  then blo.pay_amount else 0 end) as bigOrderFee \n" +
            "from business_big_lesson_order blo \n" +
            "inner join business_big_order_chance boc on boc.order_no=blo.order_no \n" +
            "where blo.pay_finish_time BETWEEN ?1 AND ?2 \n" +
            "and boc.teacher_id in ?3 " +
            "and blo.order_state ='15' \n" +
            "and blo.pay_amount > 0 \n" +
            "group by boc.teacher_id "
            , nativeQuery = true)
    List<Object[]> countBigOrderByDay(String start, String end, Collection<Long> teacherIds);

    @Query(value = "select boc.teacher_id as teacherId, \n" +
            "count(distinct case when order_state='15' and order_deposit_type!=1 then blo.order_no end) as bigOrderCount, \n" +
            "sum(case when order_state='15' then blo.pay_amount else 0 end) as bigOrderFee \n" +
            "from business_big_lesson_order blo \n" +
            "inner join business_big_order_chance boc on boc.order_no=blo.order_no \n" +
            "where blo.pay_finish_time BETWEEN ?1 AND ?2 \n" +
            "and blo.order_state ='15' \n" +
            "and blo.pay_amount > 0 \n" +
            "group by boc.teacher_id "
            , nativeQuery = true)
    List<Object[]> countBigOrderByDay(String start, String end);

    Page<BusinessBigLessonOrder> findAllByUpdateTimeBetween(Date start, Date end,  Pageable pageable);
}
